package com.ttmusic.utils.SQLite;

import java.io.File;
import java.util.Date;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Environment;

/**
 * 访问Android手机自带的SQLite数据库
 * @author Administrator
 *
 */
public class SQLiteUtil {
	
	/**数据库文件保存地址**/
	public static String DATABASE_FILE_PATH = Environment.getExternalStorageDirectory().toString()
			+ "/TTmusic/database/";
	
	/**SQLite数据库连接**/
	public static SQLiteDatabase db;
	
	/**
	 * 静态初始化块：开启数据库连接
	 */
	static {
		File f = new File(DATABASE_FILE_PATH);
		if(!f.exists())
			f.mkdirs();
		if(db == null)
			db = SQLiteDatabase.openOrCreateDatabase(DATABASE_FILE_PATH + "TTmusic.db", null);
	}
	
	
	/**
	 * 关闭数据库连接
	 */
	public static void close() {
		if(db != null) db.close();
	}
	
	/**
	 * 利用预编译执行 增删改语句
	 * @param sql
	 * @param params 可变参数  个数必须与 sql中的?占位符数量和顺序一致
	 */
	public static void executeDML(final String sql, final Object... params) {
//		new Thread(new Runnable() {
//			@Override
//			public void run() {
//				
//			}
//		}).start();
		db.execSQL(sql, params);
	}
	
	/**
	 * 利用预编译执行 查询 语句
	 * @param sql
	 * @param params
	 * @return Cursor
	 */
	public static Cursor executeQuery(String sql, String... params){
		return db.rawQuery(sql, params);
	}
	
	
	
	
//	/**
//	 * 通过主键加载歌手
//	 * @param artistId
//	 * @return 若歌手在表中不存在则返回null
//	 */
//	public static ArtistInfo loadArtistInfoById(Long artistId) {
//		String sql = "select ARTIST_ID,ARTIST,NUM_SONGS,ALBUM_ART from TB_ARTIST_INFO where ARTIST_ID=?";
//		Cursor cursor = executeQuery(sql, artistId.toString());
//		if(cursor.getCount() > 0) {
//			cursor.moveToNext();
//			String artist = cursor.getString(cursor.getColumnIndex("ARTIST"));
//			int numSongs = cursor.getInt(cursor.getColumnIndex("NUM_SONGS"));
//			String albumArt = cursor.getString(cursor.getColumnIndex("ALBUM_ART"));
//			return new ArtistInfo(artistId, artist, numSongs, albumArt);
//		}
//		return null;
//	}
//	/**
//	 * 保存艺术家信息到自己创建的TB_ARTIST_INFO表中
//	 * @param artist
//	 */
//	public static void saveArtistInfo(ArtistInfo artist) {
//		if(loadArtistInfoById(artist.getArtistId()) == null) {
//			//歌手不存在 就新增
//			String sql = "insert into TB_ARTIST_INFO(ARTIST_ID,ARTIST,NUM_SONGS,ALBUM_ART) values(?,?,?,?)";
//			executeDML(sql, artist.getArtistId(), artist.getArtist(), artist.getNumSongs(), artist.getAlbumArt());
//		}else {
//			//歌手存在就修改下歌手歌曲数量+1
//			String sql = "update TB_ARTIST_INFO set NUM_SONGS=NUM_SONGS+1 where ARTIST_ID=?";
//			executeDML(sql, artist.getArtistId());
//		}
//	}
//	/**
//	 * 通过主键加载专辑
//	 * @param albumId
//	 * @return 若专辑在表中不存在则返回null
//	 */
//	public static AlbumInfo loadAlbumInfoById(Long albumId) {
//		String sql = "select ALBUM_ID,TITLE,ARTIST,NUM_SONGS,ALBUM_ART from TB_ALBUM_INFO where ALBUM_ID=?";
//		Cursor cursor = executeQuery(sql, albumId.toString());
//		if(cursor.getCount() > 0) {
//			cursor.moveToNext();
//			String title = cursor.getString(cursor.getColumnIndex("TITLE"));
//			String artist = cursor.getString(cursor.getColumnIndex("ARTIST"));
//			int numSongs = cursor.getInt(cursor.getColumnIndex("NUM_SONGS"));
//			String albumArt = cursor.getString(cursor.getColumnIndex("ALBUM_ART"));
//			return new AlbumInfo(albumId, title, artist, numSongs, albumArt);
//		}
//		return null;
//	}
//	/**
//	 * 保存专辑信息到自己创建的TB_ALBUM_INFO表中
//	 * @param artist
//	 */
//	public static void saveAlbumInfo(AlbumInfo album) {
//		if(loadAlbumInfoById(album.getAlbumId()) == null) {
//			//专辑不存在 就新增
//			String sql = "insert into TB_ALBUM_INFO(ALBUM_ID,TITLE,ARTIST,NUM_SONGS,ALBUM_ART) values(?,?,?,?,?)";
//			executeDML(sql, album.getAlbumId(), album.getTitle(), album.getArtist(), album.getNumSongs(), album.getAlbumArt());
//		}else {
//			//专辑存在 就修改下专辑歌曲数量+1
//			String sql = "update TB_ALBUM_INFO set NUM_SONGS=NUM_SONGS+1 where ALBUM_ID=?";
//			executeDML(sql, album.getAlbumId());
//		}
//	}
//	/**
//	 * 通过主键加载歌曲信息
//	 * @param albumId
//	 * @return 若专辑在表中不存在则返回null
//	 */
//	public static MusicInfo loadMusicInfoById(Long songId) {
//		String sql = "select SONG_ID,ALBUM_ID,ARTIST_ID,TITLE,ALBUM,ARTIST,DURATION,PATH,ADD_TIME,FAVORITE from TB_MUSIC_INFO where SONG_ID=?";
//		Cursor cursor = executeQuery(sql, songId.toString());
//		if(cursor.getCount() > 0) {
//			cursor.moveToNext();
//			long albumId = cursor.getLong(cursor.getColumnIndex("ALBUM_ID"));
//			long artistId = cursor.getLong(cursor.getColumnIndex("ARTIST_ID"));
//			String title = cursor.getString(cursor.getColumnIndex("TITLE"));
//			String album = cursor.getString(cursor.getColumnIndex("ALBUM"));
//			String artist = cursor.getString(cursor.getColumnIndex("ARTIST"));
//			int duration = cursor.getInt(cursor.getColumnIndex("DURATION"));
//			String path = cursor.getString(cursor.getColumnIndex("PATH"));
//			long addTime = cursor.getLong(cursor.getColumnIndex("ADD_TIME")); 
//			int favorite = cursor.getInt(cursor.getColumnIndex("FAVORITE")); 
//			return new MusicInfo(songId, albumId, artistId, title, album, artist, duration, path, addTime, favorite);
//		}
//		return null;
//	}
//	/**
//	 * 保存歌曲信息到自己创建的TB_MUSIC_INFO表中
//	 * @param artist
//	 */
//	public static void saveMusicInfo(MusicInfo music) {
//		//歌曲不存在 就新增
//		String sql = "insert into TB_MUSIC_INFO(SONG_ID,ALBUM_ID,ARTIST_ID,TITLE,ALBUM,ARTIST,DURATION,PATH,ADD_TIME,FAVORITE) values(?,?,?,?,?,?,?,?,?,?)";
//		executeDML(sql, music.getSongId(), music.getAlbumId(), music.getArtistId(), music.getTitle(), music.getAlbum(), music.getArtist(), music.getDuration(), music.getPath(), music.getAddTime(), music.getFavorite());
//	}
	
	
	
	/**
	 * 创建TB_ARTIST_INFO表  第一次运行APP时在SplashActivity.java创建
	 * @param db
	 * @param ifNotExists
	 */
	public static void createTableTB_ARTIST_INFO(boolean ifNotExists) {
        String constraint = ifNotExists ? "IF NOT EXISTS ": "";
        db.execSQL("CREATE TABLE " + constraint + "'TB_ARTIST_INFO' (" + //
                "'ARTIST_ID' INTEGER PRIMARY KEY Autoincrement," + 	// 0: artistId
                "'ARTIST' TEXT," + 						// 1: artist
                "'NUM_SONGS' INTEGER," +				// 2: numSongs
                "'ALBUM_ART' TEXT);"); 					// 3: albumArt
    }
	
	
	/**
	 * 创建TB_ALBUM_INFO表  第一次运行APP时在SplashActivity.java创建
	 * @param ifNotExists
	 */
	public static void createTableTB_ALBUM_INFO(boolean ifNotExists) {
        String constraint = ifNotExists ? "IF NOT EXISTS ": "";
        db.execSQL("CREATE TABLE " + constraint + "'TB_ALBUM_INFO' (" + //
                "'ALBUM_ID' INTEGER PRIMARY KEY Autoincrement," + 	// 0: albumId
                "'TITLE' TEXT," + 						// 1: title
                "'ARTIST' TEXT," + 						// 2: artist
                "'NUM_SONGS' INTEGER," + 				// 3: numSongs
                "'ALBUM_ART' TEXT);"); 					// 4: albumArt
    }
	
	/**
	 * 创建TB_MUSIC_INFO表  第一次运行APP时在SplashActivity.java创建
	 * @param ifNotExists
	 */
	public static void createTableTB_MUSIC_INFO(boolean ifNotExists) {
        String constraint = ifNotExists ? "IF NOT EXISTS ": "";
        db.execSQL("CREATE TABLE " + constraint + "'TB_MUSIC_INFO' (" + //
        		"'SONG_ID' INTEGER PRIMARY KEY Autoincrement," + 	// 0: songId   歌曲id 主键
                "'ALBUM_ID' INTEGER," + 			// 1: albumId  专辑id 外键
                "'ARTIST_ID' INTEGER," + 			// 2: artistId 歌手id 外键
                "'TITLE' TEXT," + 					// 3: title	      歌曲名
                "'ALBUM' TEXT," + 					// 4: album    专辑名
                "'ARTIST' TEXT," + 					// 5: artist   歌手名
                "'DURATION' INTEGER," + 			// 6: duration 时长
                "'PATH' TEXT," + 					// 7: path     文件绝对路径
                "'ADD_TIME' BIGINT," +  			// 8: addTime  添加时间
                "'FAVORITE' INTEGER);");  			// 9: favorite 是否喜欢 1为是 0为不是
    }
	
	/**
	 * 创建歌单表 TB_MUSIC_LIST 
	 * 	创建歌单与歌曲的中间表TB_MUSICLIST_MUSICINFO
	 * @param ifNotExists
	 */
	public static void createTableTB_MUSIC_LIST(boolean ifNotExists) {
		String constraint = ifNotExists ? "IF NOT EXISTS ": "";
		//歌单表
        db.execSQL("CREATE TABLE " + constraint + "'TB_MUSIC_LIST' (" + //
        		"'LIST_ID' INTEGER PRIMARY KEY Autoincrement," + 	// 0: listId   歌单id 主键
                "'LIST_NAME' TEXT," + 				// 1: name	      歌单名
                "'NUM_SONGS' INTEGER," + 			// 2: numSongs 歌曲数量
                "'ICON_NAME' TEXT," +				// 3: 图标名称 
                "'CREATE_TIME' BIGINT);");  		// 4: 创建时间
        //歌单与歌曲的中间表
        db.execSQL("CREATE TABLE " + constraint + "'TB_MUSICLIST_MUSICINFO' (" + //
        		"'LIST_INFO_ID' INTEGER PRIMARY KEY Autoincrement," + 	// 0: listInfoId 歌单与歌曲关联关系id 主键
                "'LIST_ID' INTEGER," + 				// 1: listId   	歌单id
                "'SONG_ID' INTEGER);"); 			// 2: songId	歌曲id
	}
	
	
	/**
	 * 删除表
	 * @param tableName
	 */
	public static void dropTable(String tableName) {
		String sql = "DROP TABLE IF EXISTS " + tableName;
		db.execSQL(sql);
	}
	
	/**
	 * 清空表内所有数据
	 * @param tableName
	 */
	public static void deleteAllData(String tableName) {
		String sql = "DELETE FROM " + tableName;
		db.execSQL(sql);
	}
	
	
}
